use anyhow::{Context, Result};
use rusqlite::{Connection, OpenFlags};
use std::path::Path;
use std::sync::{Arc, Mutex};
use std::time::Duration;

/// 数据库连接池配置
#[derive(Debug, Clone)]
pub struct DatabaseConfig {
    pub max_connections: usize,
    pub connection_timeout: Duration,
    pub busy_timeout: Duration,
    pub enable_foreign_keys: bool,
    pub enable_wal_mode: bool,
    pub cache_size: i32,
}

impl Default for DatabaseConfig {
    fn default() -> Self {
        Self {
            max_connections: 10,
            connection_timeout: Duration::from_secs(30),
            busy_timeout: Duration::from_secs(5),
            enable_foreign_keys: true,
            enable_wal_mode: true,
            cache_size: -2000, // 2MB cache
        }
    }
}

/// 数据库连接包装器
pub struct DatabaseConnection {
    connection: Arc<Mutex<Connection>>,
    config: DatabaseConfig,
}

impl DatabaseConnection {
    /// 创建新的数据库连接
    pub fn new<P: AsRef<Path>>(db_path: P) -> Result<Self> {
        let config = DatabaseConfig::default();
        let connection = Connection::open_with_flags(
            db_path,
            OpenFlags::SQLITE_OPEN_READ_WRITE
                | OpenFlags::SQLITE_OPEN_CREATE
                | OpenFlags::SQLITE_OPEN_NO_MUTEX,
        )
        .context("无法打开数据库连接")?;

        // 配置数据库连接
        Self::configure_connection(&connection, &config)?;

        Ok(Self {
            connection: Arc::new(Mutex::new(connection)),
            config,
        })
    }

    /// 创建内存数据库连接
    pub fn in_memory(config: DatabaseConfig) -> Result<Self> {
        let connection = Connection::open_in_memory()
            .context("无法创建内存数据库")?;

        Self::configure_connection(&connection, &config)?;

        Ok(Self {
            connection: Arc::new(Mutex::new(connection)),
            config,
        })
    }

    /// 配置数据库连接参数
    fn configure_connection(connection: &Connection, config: &DatabaseConfig) -> Result<()> {
        // 设置忙等待超时
        connection.busy_timeout(config.busy_timeout)
            .context("设置忙等待超时失败")?;

        // 启用外键约束
        if config.enable_foreign_keys {
            connection.pragma_update(None, "foreign_keys", true)
                .context("启用外键约束失败")?;
        }

        // 启用 WAL 模式以提高并发性能
        if config.enable_wal_mode {
            connection.pragma_update(None, "journal_mode", "WAL")
                .context("启用 WAL 模式失败")?;
        }

        // 设置缓存大小
        connection.pragma_update(None, "cache_size", config.cache_size)
            .context("设置缓存大小失败")?;

        // 设置同步模式为 NORMAL 以平衡性能和安全性
        connection.pragma_update(None, "synchronous", "NORMAL")
            .context("设置同步模式失败")?;

        // 设置临时存储为内存
        connection.pragma_update(None, "temp_store", "MEMORY")
            .context("设置临时存储失败")?;

        // 启用查询优化器
        connection.execute("PRAGMA optimize", [])
            .context("启用查询优化器失败")?;

        Ok(())
    }

    /// 获取数据库连接
    pub fn get_connection(&self) -> Arc<Mutex<Connection>> {
        self.connection.clone()
    }

    /// 执行数据库健康检查
    pub fn health_check(&self) -> Result<bool> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        // 执行简单查询检查连接状态
        let result: i32 = conn.query_row("SELECT 1", [], |row| row.get(0))
            .context("数据库健康检查失败")?;

        Ok(result == 1)
    }

    /// 获取数据库统计信息
    pub fn get_stats(&self) -> Result<DatabaseStats> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let page_count: i64 = conn.query_row("PRAGMA page_count", [], |row| row.get(0))?;
        let page_size: i64 = conn.query_row("PRAGMA page_size", [], |row| row.get(0))?;
        let cache_size: i64 = conn.query_row("PRAGMA cache_size", [], |row| row.get(0))?;
        let freelist_count: i64 = conn.query_row("PRAGMA freelist_count", [], |row| row.get(0))?;

        Ok(DatabaseStats {
            page_count,
            page_size,
            cache_size,
            freelist_count,
            database_size: page_count * page_size,
            free_space: freelist_count * page_size,
        })
    }

    /// 优化数据库
    pub fn optimize(&self) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        // 分析查询计划
        conn.execute("ANALYZE", [])
            .context("分析数据库失败")?;

        // 优化数据库
        conn.execute("PRAGMA optimize", [])
            .context("优化数据库失败")?;

        // 清理未使用的页面
        conn.execute("VACUUM", [])
            .context("清理数据库失败")?;

        log::info!("数据库优化完成");
        Ok(())
    }

    /// 备份数据库
    pub fn backup<P: AsRef<Path>>(&self, backup_path: P) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let mut backup_conn = Connection::open(&backup_path)
            .context("创建备份数据库连接失败")?;

        let backup = rusqlite::backup::Backup::new(&*conn, &mut backup_conn)
            .context("创建数据库备份失败")?;

        backup.run_to_completion(5, Duration::from_millis(250), None)
            .context("执行数据库备份失败")?;

        log::info!("数据库备份完成: {:?}", backup_path.as_ref());
        Ok(())
    }

    /// Get application configuration
    pub fn get_config(&self) -> Result<crate::database::AppConfig> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        // Try to get existing config from database
        let mut stmt = conn.prepare("SELECT * FROM app_config WHERE id = 'default' LIMIT 1")
            .context("准备查询配置语句失败")?;

        let config_result = stmt.query_row([], |row| {
            crate::database::AppConfig::from_row(row)
        });

        match config_result {
            Ok(config) => Ok(config),
            Err(rusqlite::Error::QueryReturnedNoRows) => {
                // No config exists, create and return default
                let default_config = crate::database::AppConfig::default();

                // Save default config to database
                if let Err(e) = self.save_config(&default_config) {
                    log::warn!("Failed to save default config: {}", e);
                }

                Ok(default_config)
            }
            Err(e) => {
                log::error!("Failed to query config: {}", e);
                Err(anyhow::anyhow!("查询配置失败: {}", e))
            }
        }
    }

    /// Save application configuration
    pub fn save_config(&self, config: &crate::database::AppConfig) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let shortcuts_json = serde_json::to_string(&config.shortcuts)
            .context("序列化快捷键配置失败")?;

        // Use INSERT OR REPLACE to handle both insert and update cases
        conn.execute(
            "INSERT OR REPLACE INTO app_config (
                id, theme, language, auto_start, minimize_to_tray,
                notifications, shortcuts, created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
            rusqlite::params![
                config.id,
                config.theme,
                config.language,
                config.auto_start,
                config.minimize_to_tray,
                config.notifications,
                shortcuts_json,
                config.created_at.to_rfc3339(),
                config.updated_at.to_rfc3339(),
            ],
        ).context("保存配置到数据库失败")?;

        log::info!("Configuration saved successfully: language={}, theme={}",
                   config.language, config.theme);
        Ok(())
    }

    /// Get log entries
    pub fn get_logs(&self, limit: Option<i32>, level_filter: Option<&str>) -> Result<Vec<crate::database::LogEntry>> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let mut query = "SELECT id, level, message, module, metadata, created_at FROM logs".to_string();
        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();

        // 添加级别过滤
        if let Some(level) = level_filter {
            if !level.is_empty() {
                query.push_str(" WHERE level = ?");
                params.push(Box::new(level.to_string()));
            }
        }

        // 添加排序和限制
        query.push_str(" ORDER BY created_at DESC");
        if let Some(limit) = limit {
            query.push_str(" LIMIT ?");
            params.push(Box::new(limit));
        }

        let mut stmt = conn.prepare(&query)?;
        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();

        let log_iter = stmt.query_map(&param_refs[..], |row| {
            Ok(crate::database::LogEntry {
                id: row.get(0)?,
                level: row.get(1)?,
                message: row.get(2)?,
                component: row.get(3)?,
                metadata: row.get::<_, Option<String>>(4)?.and_then(|s| serde_json::from_str(&s).ok()),
                created_at: chrono::DateTime::parse_from_rfc3339(&row.get::<_, String>(5)?)
                    .map_err(|_| rusqlite::Error::InvalidColumnType(5, "created_at".to_string(), rusqlite::types::Type::Text))?
                    .with_timezone(&chrono::Utc),
            })
        })?;

        let mut logs = Vec::new();
        for log in log_iter {
            logs.push(log?);
        }

        Ok(logs)
    }

    /// Add a log entry
    pub fn add_log(&self, level: &str, message: &str, component: Option<&str>) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let now = chrono::Utc::now().to_rfc3339();

        conn.execute(
            "INSERT INTO logs (level, message, module, created_at) VALUES (?1, ?2, ?3, ?4)",
            rusqlite::params![level, message, component, now],
        )?;

        Ok(())
    }

    /// Add a log entry with a specific timestamp (for testing purposes)
    pub fn add_log_with_timestamp(&self, level: &str, message: &str, component: Option<&str>, timestamp: &str) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        conn.execute(
            "INSERT INTO logs (level, message, module, created_at) VALUES (?1, ?2, ?3, ?4)",
            rusqlite::params![level, message, component, timestamp],
        )?;

        Ok(())
    }

    /// Clear old log entries (keep logs newer than specified days)
    pub fn clear_old_logs(&self, days_to_keep: i32) -> Result<usize> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let deleted_count = conn.execute(
            "DELETE FROM logs WHERE created_at < datetime('now', '-' || ? || ' days')",
            rusqlite::params![days_to_keep],
        )?;

        Ok(deleted_count)
    }

    /// Clear log entries older than specified days
    pub fn clear_logs_before_days(&self, days_before: i32) -> Result<usize> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let deleted_count = conn.execute(
            "DELETE FROM logs WHERE created_at < datetime('now', '-' || ? || ' days')",
            rusqlite::params![days_before],
        )?;

        Ok(deleted_count)
    }

    /// Get log statistics
    pub fn get_log_stats(&self) -> Result<serde_json::Value> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        // 获取总数
        let total: i64 = conn.query_row("SELECT COUNT(*) FROM logs", [], |row| row.get(0))
            .unwrap_or(0);

        // 获取各级别统计
        let debug_count: i64 = conn.query_row("SELECT COUNT(*) FROM logs WHERE level = 'DEBUG'", [], |row| row.get(0))
            .unwrap_or(0);
        let info_count: i64 = conn.query_row("SELECT COUNT(*) FROM logs WHERE level = 'INFO'", [], |row| row.get(0))
            .unwrap_or(0);
        let warn_count: i64 = conn.query_row("SELECT COUNT(*) FROM logs WHERE level = 'WARN'", [], |row| row.get(0))
            .unwrap_or(0);
        let error_count: i64 = conn.query_row("SELECT COUNT(*) FROM logs WHERE level = 'ERROR'", [], |row| row.get(0))
            .unwrap_or(0);
        let fatal_count: i64 = conn.query_row("SELECT COUNT(*) FROM logs WHERE level = 'FATAL'", [], |row| row.get(0))
            .unwrap_or(0);

        // 获取最近24小时的日志数量
        let recent_count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM logs WHERE created_at > datetime('now', '-1 day')",
            [],
            |row| row.get(0)
        ).unwrap_or(0);

        Ok(serde_json::json!({
            "total": total,
            "by_level": {
                "DEBUG": debug_count,
                "INFO": info_count,
                "WARN": warn_count,
                "ERROR": error_count,
                "FATAL": fatal_count
            },
            "recent_count": recent_count
        }))
    }

    /// Run database migrations
    pub fn run_migrations(&self) -> Result<()> {
        let conn = self.connection.lock()
            .map_err(|e| anyhow::anyhow!("获取数据库连接锁失败: {}", e))?;

        let mut migration_manager = crate::database::migrations::MigrationManager::new();
        migration_manager.migrate(&conn)?;

        Ok(())
    }
}

/// 数据库统计信息
#[derive(Debug, Clone)]
pub struct DatabaseStats {
    pub page_count: i64,
    pub page_size: i64,
    pub cache_size: i64,
    pub freelist_count: i64,
    pub database_size: i64,
    pub free_space: i64,
}

/// 简单的连接池实现
pub struct ConnectionPool {
    connections: Vec<DatabaseConnection>,
    config: DatabaseConfig,
    current_index: Arc<Mutex<usize>>,
}

impl ConnectionPool {
    /// 创建新的连接池
    pub fn new<P: AsRef<Path>>(db_path: P, config: DatabaseConfig) -> Result<Self> {
        let mut connections = Vec::with_capacity(config.max_connections);
        
        for _ in 0..config.max_connections {
            let conn = DatabaseConnection::new(&db_path)?;
            connections.push(conn);
        }

        Ok(Self {
            connections,
            config,
            current_index: Arc::new(Mutex::new(0)),
        })
    }

    /// 获取连接（轮询方式）
    pub fn get_connection(&self) -> Result<&DatabaseConnection> {
        let mut index = self.current_index.lock()
            .map_err(|e| anyhow::anyhow!("获取连接池索引锁失败: {}", e))?;

        let conn = &self.connections[*index];
        *index = (*index + 1) % self.connections.len();

        Ok(conn)
    }

    /// 检查所有连接的健康状态
    pub fn health_check_all(&self) -> Result<Vec<bool>> {
        let mut results = Vec::new();
        
        for conn in &self.connections {
            results.push(conn.health_check()?);
        }

        Ok(results)
    }

    /// 优化所有连接
    pub fn optimize_all(&self) -> Result<()> {
        for conn in &self.connections {
            conn.optimize()?;
        }
        Ok(())
    }
}
